package org.shock.ss.sms.commons.db;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.shock.ss.sms.bean.system.ProjectLog;

/**
 * 常用db操作类，用于非事务处理!注意区别于TsDbTools.java
 * 
 * @author jile
 * 
 */
public class DbTools {
	private static Connection conn;
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();// 用来把Connection绑定到当前线程上的变量

	public static String URLEncoder(String s) {
		if (s == null) {
			s = "";
		}
		try {
			s = java.net.URLEncoder.encode(s, "UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return s;
	}

	/**
	 * 返回一个list提供给strust2的select使用
	 * 
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static List getList(String sql) throws Exception {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		List list = new ArrayList();
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Map map = new HashMap();
				map.put("value", rs.getString(2));
				map.put("text", rs.getString(1));
				list.add(map);
				// throw new Exception("人工调试错误");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(tl.get());
		}
		return list;
	}

	public static String getAllResult(String sql, String key) {

		PreparedStatement stmt = null;
		ResultSet rs = null;
		String result = "";
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				result += rs.getString(key) + ",";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(tl.get());
		}
		return result + 0;
	}

	/** 输入sql语句得到一个字符串查询结果，无结果返回"" */
	public static String getString(String sql) {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		/** 待注释 */
		String resultstr = "";
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				resultstr = rs.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(tl.get());
		}
		// System.out.println("返回字符串------------>" + resultstr);
		return resultstr;
	}

	/** 输入sql语句得到List一个字符串查询结果，无结果返回"" */
	public static ArrayList getStringList(String sql) {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		ArrayList list = new ArrayList();
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				list.add(rs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(tl.get());
		}
		// System.out.println("返回List------------>" + list);
		return list;
	}

	/** 输入sql语句得到一个整型查询结果，无结果返回-1 */
	public static int getInt(String sql) {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		int result = 0;
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(tl.get());
		}
		// System.out.println("返回整型------------>" + result);
		return result;
	}

	/**
	 * 执行一条算sql语句
	 * 
	 * @throws SQLException
	 */
	public static boolean doSql(String sql) throws SQLException {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		boolean result = true;
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			conn.setAutoCommit(false);
			stmt = conn.prepareStatement(sql);
			stmt.execute();
			conn.commit();
		} catch (Exception e) {
			result = false;
			conn.rollback();
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(tl.get());
		}
		// System.out.println(result);
		return result;
	}

	/** 根据sql语句得到一个map，key值就是数据库字段名，value值均为String类型 */
	public static Map getMap(String sql) {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		Map map = null;
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				map = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					String name = rsmd.getColumnName(i);
					map.put(name.toLowerCase(), rs.getString(i));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(tl.get());
		}
		// System.out.println("返回map--------->" + map);
		return map;
	}

	/** 查询身份证 */

	/** 根据sql语句得到一个list，key值就是数据库字段名，value值均为String类型 */
	public static List getMapsInList(String sql) {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		Map map = null;
		ArrayList list = new ArrayList();
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				map = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					String name = rsmd.getColumnName(i);
					map.put(name.toLowerCase(), rs.getString(i));
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(tl.get());
		}
		// System.out.println("返回map--------->" + list);
		return list;
	}

	/** 此方法是用来完成Dwr二级联动的 */
	public static Map getMapForSelect(String sqlStr) {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		int flag = 0;
		Map teacherinfor = new HashMap();
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			stmt = conn.prepareStatement(sqlStr);
			rs = stmt.executeQuery();
			while (rs.next()) {
				teacherinfor.put(String.valueOf(rs.getInt(1)), rs.getString(2)); //			
			}
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(tl.get());
		}
		return teacherinfor;
	}

	public static Long NextKey(String tablename, String key) throws Exception {
		return Long.parseLong(getString("select seq_" + tablename + ".nextval from dual"));
	}

	public static void addLog(ProjectLog log) {
		PreparedStatement ps = null;
		try {
			conn = ProxoolDbPool.getConnection();
			tl.set(conn);
			String sql = "insert into ckgl_projectlog (type,opzh,optime,opip,content,bgcontent,id1,id2,code1,code2,bz)values(?,?,?,?,?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, log.getType());
			ps.setString(2, log.getOpzh());
			ps.setString(3, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(System.currentTimeMillis())));
			ps.setString(4, log.getOpip());
			ps.setString(5, log.getContent());
			ps.setObject(6, log.getBgcontent());
			ps.setObject(7, log.getId1());
			ps.setObject(8, log.getId2());
			ps.setString(9, log.getCode1());
			ps.setString(10, log.getCode2());
			ps.setString(11, log.getBz());
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeStatement(ps);
			ProxoolDbPool.closeConnection(tl.get());
		}
	}

	public static List<Long> getSubIdsList(List<Long> result, String table, String pidField, String pid) {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		try {
			conn = DriverManager.getConnection("proxool.MyPool");
			String sql = "select id from " + table + " where " + pidField + "=" + pid;
			System.out.println(sql);
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				result.add(rs.getLong("id"));
				getSubIdsList(result, table, pidField, rs.getLong("id") + "");
			}
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(conn);
			return result;
		}
	}

	public static String getSubIdsStr(List<Long> result, String table, String pidField, String pid) {
		StringBuffer sb = new StringBuffer();
		List<Long> list = getSubIdsList(result, table, pidField, pid);
		for (Long id : list) {
			sb.append(id + ",");
		}
		if (sb.lastIndexOf(",") != -1) {
			sb.deleteCharAt(sb.lastIndexOf(","));
		}
		if (sb.length() == 0) {
			sb.append("0");
		}
		return sb.toString();
	}
}
